package com.classes;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Random;
import java.util.TreeMap;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.util.StringUtil;
import org.json.simple.JSONObject;
import org.json.simple.JSONValue;

import com.mbbmap.app.manager.ConfigManager;
import com.mbbmap.app.manager.DBConnectionManager;
import com.mbbmap.util.Constants;

public class OutletFactory {
	private String message;
	private long outletID = 0;
	
	public String getMessage() {
		return "[OutletFactory] " + this.message; 
	}
	
	public long getOutletID() {
		return this.outletID; 
	}
	
	public String generateVendorFormSerialNo() {
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				Long serial_no = (long) 0;
				
				Statement statement = conn.createStatement();
				
				try {
					ResultSet rs = statement.executeQuery("SELECT SEQ_VENDOR_SERIALNO.NEXTVAL FROM DUAL");
					
					String serial_str = "";
					try {
						if (rs.next()) {
							serial_no = new Long(rs.getLong(1));
						}
						
						serial_str = Long.toString(serial_no, 36);
						serial_str = String.format("%8s", serial_str);
						serial_str = serial_str.replace(" ", "0");
						
					} finally {
						rs.close();
					}
					
					
					SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
		
					return (sdf.format(new Date()) + serial_str);
					
					
				} finally {
					statement.close();
				}
				
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
		} catch (Exception e){
			this.message = e.getMessage();
			return null;
		}
	}
	
	public String generateVendorFormSerialNo(int case_id, int outlet_id, String refference_no, String vendor_name) {
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				boolean isExisted = false;
				List<String> lstSerialNo = new ArrayList<String>();
				List<String> lstVendorName = new ArrayList<String>();
				HashMap<String, String> hash = new HashMap<String, String>();
				
				String sql = "select POS_SERIAL_NO, POS_VENDOR_NAME from CASEPOSFORM where POS_CASE_ID = ? and POS_OUTLET_ID = ?";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				try{
					statement.setInt(1, case_id);
					statement.setInt(2, outlet_id);
					
					ResultSet resultSet = statement.executeQuery();
					try {
						while(resultSet.next()){
							String posSerialNo= resultSet.getString("POS_SERIAL_NO");
							String posVendorName = resultSet.getString("POS_VENDOR_NAME");
							
							hash.put(posVendorName, posSerialNo);
							
						}
					}finally {
						resultSet.close();
					}
				}finally{
					statement.close();
				}
				
				do{
					if(hash != null && hash.size() > 0){
						if(hash.containsKey(vendor_name))
							return hash.get(vendor_name);
					}
					
					char randomChar = randomCharacter();
					String randomNum = randomNumber(0, 9);
					String serialNo = refference_no + randomChar + randomNum;
					
					if(lstSerialNo != null && lstSerialNo.size() > 0){
						if(lstSerialNo.contains(serialNo)){
							isExisted = true;
						}else{
							return serialNo;
						}
					}else{
						return serialNo;
					}	
				}while(isExisted == true);
				
			}finally {
				connMgr.freeConnection(connectionPool, conn);
			}			
		} catch (Exception e){
			this.message = e.getMessage();
		}
		return "";
	}
	
	
	public boolean newCaseOutlet(TreeMap<String, String> post, String user){
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String sql = "insert into outlet (o_merchant_id, o_name, o_reg_name, o_reg_no, "
						+ "o_license_no, o_license_type, o_license_issuer, o_license_state, "
						+ "o_license_expiry, o_risk, o_ct_name, o_ct_designation, o_ct_email, "
						+ "o_ct_tel, o_ct_fax, o_created_date, o_created_by, o_modified_date, "
						+ "o_modified_by) "
						+ "values (?, ?, ?, ?, "
						+ "?, ?, ?, ?, "
						+ "?, ?, ?, ?, ?, "
						+ "?, ?, sysdate, ?, sysdate, "
						+ "?)";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					statement.setInt(1, Integer.parseInt(post.get("merchant_id")));
					statement.setString(2, post.get("trading_name"));
					statement.setString(3, post.get("registered_name"));
					statement.setString(4, post.get("registered_no"));
					statement.setString(5, post.get("license_no"));
					statement.setString(6, post.get("license_type"));
					statement.setString(7, post.get("issuer"));
					statement.setString(8, post.get("state"));
					statement.setString(9, post.get("expiry_date"));
					statement.setString(10, post.get("risk_level"));
					statement.setString(11, post.get("contact_person"));
					statement.setString(12, post.get("designation"));
					statement.setString(13, post.get("email"));
					statement.setString(14, post.get("telephone_no"));
					statement.setString(15, post.get("fax_no"));
					statement.setString(16, user);
					statement.setString(17, user);
					
					statement.execute();
					
				} finally {
					statement.close();
				}
				
				Long outlet_id = null;
				Statement statement2 = conn.createStatement();
				
				try {
					ResultSet generatedKeys = statement2.executeQuery("SELECT SEQ_OUTLET.CURRVAL FROM DUAL");
					try {
						if (generatedKeys.next()) {
							outlet_id = new Long(generatedKeys.getLong(1));
						}
					} finally {
						generatedKeys.close();
					}
				} finally {
					statement2.close();
				}
				
				this.outletID = outlet_id;
				System.out.println("Inserted Outlet ID: " + this.outletID);
				
				
				JSONObject outlet_dtl_obj = new JSONObject();
				outlet_dtl_obj.put("merchant_id", post.get("merchant_id"));
				outlet_dtl_obj.put("trading_name", post.get("trading_name"));
				outlet_dtl_obj.put("registered_name", post.get("registered_name"));
				outlet_dtl_obj.put("registered_no", post.get("registered_no"));
				outlet_dtl_obj.put("license_no", post.get("license_no"));
				outlet_dtl_obj.put("license_type", post.get("license_type"));
				outlet_dtl_obj.put("issuer", post.get("issuer"));
				outlet_dtl_obj.put("state", post.get("state"));
				outlet_dtl_obj.put("expiry_date", post.get("expiry_date"));
				outlet_dtl_obj.put("risk_level", post.get("risk_level"));
				outlet_dtl_obj.put("contact_person", post.get("contact_person"));
				outlet_dtl_obj.put("gst_registration", post.get("gst_registration"));
				outlet_dtl_obj.put("designation", post.get("designation"));
				outlet_dtl_obj.put("email", post.get("email"));
				outlet_dtl_obj.put("telephone_no", post.get("telephone_no"));
				outlet_dtl_obj.put("fax_no", post.get("fax_no"));
				outlet_dtl_obj.put("address", "");
				outlet_dtl_obj.put("address_2", "");
				outlet_dtl_obj.put("address_3", "");
				outlet_dtl_obj.put("mdr_rate", "");
				outlet_dtl_obj.put("esapl", "");
				outlet_dtl_obj.put("rmcd_approval_no", post.get("rmcd_approval_no"));
				outlet_dtl_obj.put("ezypay", "");
				outlet_dtl_obj.put("stmt_option", "");
				outlet_dtl_obj.put("pymt_option", "");
				outlet_dtl_obj.put("other_info", "");
				String outlet_dtl = outlet_dtl_obj.toString();
				
				sql = "insert into caseoutlet (co_case_id, co_outlet_id, co_outlet_details, co_esa_ref_no, co_num_terminal, co_trm_rental, co_trm_owner, co_trm_deposit_wired, co_trm_deposit_wireless, co_trm_rental_deposit_wired, co_trm_rental_deposit_wireless, co_trm_rental_advance, co_cheque_amt, co_cheque_issuer, co_cheque_no, co_cheque_date, co_terminal_id, co_terminal_id_no, co_terminal_install_date) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
				
				statement = conn.prepareStatement(sql);
				
				try {
					statement.setInt(1, Integer.parseInt(post.get("case_id")));
					statement.setInt(2, Integer.parseInt(this.outletID + ""));
					statement.setString(3, outlet_dtl);
					statement.setString(4, post.get("esa_ref_no"));
					statement.setString(5, post.get("no_of_terminal"));
					statement.setString(6, post.get("terminal_rental_fee"));
					statement.setString(7, post.get("terminal_owner"));
					statement.setDouble(8, (post.get("terminal_deposit_wired").length() <= 0 ? 0.00 : Double.parseDouble(post.get("terminal_deposit_wired"))));
					statement.setDouble(9, (post.get("terminal_deposit_wireless").length() <= 0 ? 0.00 : Double.parseDouble(post.get("terminal_deposit_wireless"))));
					statement.setDouble(10, (post.get("terminal_rental_deposit_wired").length() <= 0 ? 0.00 : Double.parseDouble(post.get("terminal_rental_deposit_wired"))));
					statement.setDouble(11, (post.get("terminal_rental_deposit_wireless").length() <= 0 ? 0.00 : Double.parseDouble(post.get("terminal_rental_deposit_wireless"))));
					statement.setDouble(12, (post.get("advance_terminal_rental").length() <= 0 ? 0.00 : Double.parseDouble(post.get("advance_terminal_rental"))));
					statement.setDouble(13, (post.get("total_cheque_amt").length() <= 0 ? 0.00 : Double.parseDouble(post.get("total_cheque_amt"))));
					statement.setString(14, post.get("bank_issuer") + "|" + post.get("bank_branch"));
					statement.setString(15, post.get("cheque_no"));
					statement.setString(16, post.get("cheque_date"));
					statement.setString(17, post.get("terminal_id"));
					statement.setString(18, post.get("terminal_id_no"));
					statement.setString(19, post.get("terminal_install_date"));
					
					statement.execute();
				} finally {
					statement.close();
				}
				
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return true;
			
		} catch (Exception e){
			this.message = e.getMessage();
			return false;
		}
	}
	
	public Outlet getOutlet(int outlet_id) {
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String sql = "select * from outlet where o_id = ?";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					statement.setInt(1, outlet_id);
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						if(resultSet.next()){
							Outlet o = new Outlet();
							
							o.setId(Long.parseLong(resultSet.getString("o_id")));
							o.setName(resultSet.getString("o_name"));
							o.setLicenseNo(resultSet.getString("o_license_no"));
							o.setLicenseType(resultSet.getString("o_license_type"));
							o.setLicenseIssuer(resultSet.getString("o_license_issuer"));
							o.setLicenseState(resultSet.getString("o_license_state"));
							o.setLicenseExpiry(resultSet.getString("o_license_expiry"));
							o.setRiskLevel(resultSet.getString("o_risk"));
							o.setCTName(resultSet.getString("o_ct_name"));
							o.setCTDesignation(resultSet.getString("o_ct_designation"));
							o.setCTEmail(resultSet.getString("o_ct_email"));
							o.setCTTel(resultSet.getString("o_ct_tel"));
							o.setCTFax(resultSet.getString("o_ct_fax"));
							o.setPaymentOpt(resultSet.getString("o_pymt_option"));
							o.setStatementOpt(resultSet.getString("o_stmt_option"));
							o.setOtherInfo(resultSet.getString("o_other_info"));
							o.setAddress(resultSet.getString("o_address"));
							o.setAddress2(resultSet.getString("o_address_2"));
							o.setAddress3(resultSet.getString("o_address_3"));
							
							Merchant m = new Merchant();
							m.setId(Long.parseLong(resultSet.getString("o_merchant_id")));
							m.setRegisteredName(resultSet.getString("o_reg_name"));
							m.setRegisteredNo(resultSet.getString("o_reg_no"));
							
							o.setMerchant(m);
							
							return o;
						}
					} finally {
						resultSet.close();
					}
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			this.message = "Outlet not found.";
			
		} catch (Exception e){
			this.message = e.getMessage();
		}
		
		return null;
	}
	
	public CaseOutlet getCaseOutlet(int case_id, int outlet_id) {
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String sql = "select * from caseoutlet where co_case_id = ? and co_outlet_id = ?";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					statement.setInt(1, case_id);
					statement.setInt(2, outlet_id);
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						if(resultSet.next()){
							CaseOutlet a = new CaseOutlet();
							
							a.setCaseId(Long.parseLong(resultSet.getString("co_case_id")));
							a.setOutletDtl(resultSet.getString("co_outlet_details"));
							a.setESARefNo(resultSet.getString("co_esa_ref_no"));
							a.setNumTerminal(resultSet.getString("co_num_terminal"));
							a.setTrmRental(resultSet.getString("co_trm_rental"));
							a.setTrmOwner(resultSet.getString("co_trm_owner"));
							a.setTrmDepositWired(resultSet.getString("co_trm_deposit_wired"));
							a.setTrmDepositWireless(resultSet.getString("co_trm_deposit_wireless"));
							a.setTrmRentalDepositWired(resultSet.getString("co_trm_rental_deposit_wired"));
							a.setTrmRentalDepositWireless(resultSet.getString("co_trm_rental_deposit_wireless"));
							a.setTrmRentalAdvance(resultSet.getString("co_trm_rental_advance"));
							a.setChequeAmt(resultSet.getString("co_cheque_amt"));
							a.setChequeIssuer(resultSet.getString("co_cheque_issuer"));
							a.setChequeNo(resultSet.getString("co_cheque_no"));
							a.setChequeDate(resultSet.getString("co_cheque_date"));
							a.setTerminalID(resultSet.getString("co_terminal_id"));
							a.setTerminalIDNo(resultSet.getString("co_terminal_id_no"));
							a.setTerminalInstallDate(resultSet.getString("co_terminal_install_date"));
							a.setRecommendation(resultSet.getString("co_recommendation"));
							a.setPosForm(resultSet.getString("co_pos_form"));
							a.setPosInstallation(resultSet.getString("co_installation"));
							a.setMidPOS(resultSet.getString("co_mid_pos"));
							a.setMidAMEX(resultSet.getString("co_mid_amex"));
							
							JSONObject obj = (JSONObject)JSONValue.parse(resultSet.getString("co_outlet_details"));
			
							Outlet o = new Outlet();
							o.setId(Long.parseLong(resultSet.getString("co_outlet_id")));
							o.setName(obj.get("trading_name").toString());
							o.setLicenseNo(obj.get("license_no").toString());
							o.setLicenseType(obj.get("license_type").toString());
							o.setLicenseIssuer(obj.get("issuer").toString());
							o.setLicenseState(obj.get("state").toString());
							o.setLicenseExpiry(obj.get("expiry_date").toString());
							o.setRiskLevel(obj.get("risk_level").toString());
							o.setCTName(obj.get("contact_person").toString());
							o.setCTDesignation(obj.get("designation").toString());
							o.setCTEmail(obj.get("email").toString());
							o.setCTTel(obj.get("telephone_no").toString());
							o.setCTFax(obj.get("fax_no").toString());
							o.setPaymentOpt(resultSet.getString("co_pymt_option"));
							o.setStatementOpt(resultSet.getString("co_stmt_option"));
							o.setOtherInfo(resultSet.getString("co_other_info"));
							o.setAddress(resultSet.getString("co_address"));
							o.setAddress2(resultSet.getString("co_address_2"));
							o.setAddress3(resultSet.getString("co_address_3"));
							o.setMDRRate(resultSet.getString("co_mdr_rate"));
							o.setEsapl(resultSet.getString("co_esapl"));
							o.setEzypay(resultSet.getString("co_ezy"));
							
							Merchant m = new Merchant();
							m.setId(Long.parseLong(obj.get("merchant_id").toString()));
							m.setRegisteredName(obj.get("registered_name").toString());
							m.setRegisteredNo(obj.get("registered_no").toString());
							
							o.setMerchant(m);
							
							a.setOutlet(o);
							
							return a;
						}
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			this.message = "Case Outlet not found.";
			
		} catch (Exception e){
			this.message = e.getMessage();
		}
		
		return null;
	}
	
	public CaseOutlet getCaseOutletBySerialNo(String serial_no) {
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String sql = "select * from caseoutlet "
						+ "left join caseposform on co_case_id = pos_case_id and co_outlet_id = pos_outlet_id "
						+ "where pos_serial_no = ?";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					statement.setString(1, serial_no);
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						if(resultSet.next()){
							CaseOutlet a = new CaseOutlet();
							
							a.setCaseId(Long.parseLong(resultSet.getString("co_case_id")));
							a.setOutletDtl(resultSet.getString("co_outlet_details"));
							a.setESARefNo(resultSet.getString("co_esa_ref_no"));
							a.setNumTerminal(resultSet.getString("co_num_terminal"));
							a.setTrmRental(resultSet.getString("co_trm_rental"));
							a.setTrmOwner(resultSet.getString("co_trm_owner"));
							a.setTrmDepositWired(resultSet.getString("co_trm_deposit_wired"));
							a.setTrmDepositWireless(resultSet.getString("co_trm_deposit_wireless"));
							a.setTrmRentalDepositWired(resultSet.getString("co_trm_rental_deposit_wired"));
							a.setTrmRentalDepositWireless(resultSet.getString("co_trm_rental_deposit_wireless"));
							a.setTrmRentalAdvance(resultSet.getString("co_trm_rental_advance"));
							a.setChequeAmt(resultSet.getString("co_cheque_amt"));
							a.setChequeIssuer(resultSet.getString("co_cheque_issuer"));
							a.setChequeNo(resultSet.getString("co_cheque_no"));
							a.setChequeDate(resultSet.getString("co_cheque_date"));
							a.setTerminalID(resultSet.getString("co_terminal_id"));
							a.setTerminalIDNo(resultSet.getString("co_terminal_id_no"));
							a.setTerminalInstallDate(resultSet.getString("co_terminal_install_date"));
							a.setRecommendation(resultSet.getString("co_recommendation"));
							a.setPosForm(resultSet.getString("co_pos_form"));
							a.setPosInstallation(resultSet.getString("co_installation"));
							a.setMidPOS(resultSet.getString("co_mid_pos"));
							a.setMidAMEX(resultSet.getString("co_mid_amex"));
							a.setVendor_name(resultSet.getString("pos_vendor_name"));
							
							JSONObject obj = (JSONObject)JSONValue.parse(resultSet.getString("co_outlet_details"));
			
							Outlet o = new Outlet();
							o.setId(Long.parseLong(resultSet.getString("co_outlet_id")));
							o.setName(obj.get("trading_name").toString());
							o.setLicenseNo(obj.get("license_no").toString());
							o.setLicenseType(obj.get("license_type").toString());
							o.setLicenseIssuer(obj.get("issuer").toString());
							o.setLicenseState(obj.get("state").toString());
							o.setLicenseExpiry(obj.get("expiry_date").toString());
							o.setRiskLevel(obj.get("risk_level").toString());
							o.setCTName(obj.get("contact_person").toString());
							o.setCTDesignation(obj.get("designation").toString());
							o.setCTEmail(obj.get("email").toString());
							o.setCTTel(obj.get("telephone_no").toString());
							o.setCTFax(obj.get("fax_no").toString());
							o.setPaymentOpt(resultSet.getString("co_pymt_option"));
							o.setStatementOpt(resultSet.getString("co_stmt_option"));
							o.setOtherInfo(resultSet.getString("co_other_info"));
							o.setAddress(resultSet.getString("co_address"));
							o.setAddress2(resultSet.getString("co_address_2"));
							o.setAddress3(resultSet.getString("co_address_3"));
							o.setMDRRate(resultSet.getString("co_mdr_rate"));
							o.setEsapl(resultSet.getString("co_esapl"));
							o.setEzypay(resultSet.getString("co_ezy"));
							
							Merchant m = new Merchant();
							m.setId(Long.parseLong(obj.get("merchant_id").toString()));
							m.setRegisteredName(obj.get("registered_name").toString());
							m.setRegisteredNo(obj.get("registered_no").toString());
							
							o.setMerchant(m);
							
							a.setOutlet(o);
							
							return a;
						}
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			this.message = "Case Outlet not found.";
			
		} catch (Exception e){
			this.message = e.getMessage();
		}
		
		return null;
	}
	
	public boolean editCaseOutlet(int case_id, int outlet_id, TreeMap<String, String> post, String user){
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String sql = "update outlet set o_modified_date = sysdate, o_modified_by = ?, ";
				
				if(post.get("payment_info") != null) {
					sql += "o_pymt_option = ?, ";
				}
				
				if(post.get("statement_opt") != null) {
					sql += "o_stmt_option = ?, ";
				}
				
				if(post.get("other_info") != null) {
					sql += "o_other_info = ?, ";
				}
				
				if(post.get("physical_address") != null) {
					sql += "o_address = ?, ";
				}
				
				if(post.get("payment_address") != null) {
					sql += "o_address_2 = ?, ";
				}
				
				if(post.get("correspondence_address") != null) {
					sql += "o_address_3 = ?, ";
				}
				
				if(post.get("registered_name") != null) {
					sql += "o_reg_name = ?, ";
				}
				
				if(post.get("registered_no") != null) {
					sql += "o_reg_no = ?, ";
				}
				
				if(post.get("trading_name") != null) {
					sql += "o_name = ?, ";
				}
				
				if(post.get("contact_person") != null) {
					sql += "o_ct_name = ?, ";
				}
				
				if(post.get("designation") != null) {
					sql += "o_ct_designation = ?, ";
				}
				
				if(post.get("email") != null) {
					sql += "o_ct_email = ?, ";
				}
				
				if(post.get("telephone_no") != null) {
					sql += "o_ct_tel = ?, ";
				}
				
				if(post.get("fax_no") != null) {
					sql += "o_ct_fax = ?, ";
				}
				
				if(post.get("license_no") != null) {
					sql += "o_license_no = ?, ";
				}
				
				if(post.get("license_type") != null) {
					sql += "o_license_type = ?, ";
				}
				
				if(post.get("state") != null) {
					sql += "o_license_state = ?, ";
				}
				
				if(post.get("issuer") != null) {
					sql += "o_license_issuer = ?, ";
				}
				
				if(post.get("expiry_date") != null) {
					sql += "o_license_expiry = ?, ";
				}
				
				if(post.get("risk_level") != null) {
					sql += "o_risk = ?, ";
				}
	
				if(post.get("mdr") != null) {
					sql += "o_mdr_rate = ?, ";
				}
				
				if(post.get("esapl") != null) {
					sql += "o_esapl = ?, ";
				}
				
				if(post.get("ezypay") != null) {
					sql += "o_ezy = ?, ";
				}
				sql = sql.substring(0, sql.lastIndexOf(','));
				
				sql += " where o_id = ?";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					int paramIndex = 1;
					statement.setString(paramIndex++, user);
					
					if(post.get("payment_info") != null) {
						statement.setString(paramIndex++, post.get("payment_info"));
					}
					
					if(post.get("statement_opt") != null) {
						statement.setString(paramIndex++, post.get("statement_opt"));
					}
					
					if(post.get("other_info") != null) {
						statement.setString(paramIndex++, post.get("other_info"));
					}
					
					if(post.get("physical_address") != null) {
						statement.setString(paramIndex++, post.get("physical_address"));
					}
					
					if(post.get("payment_address") != null) {
						statement.setString(paramIndex++, post.get("payment_address"));
					}
					
					if(post.get("correspondence_address") != null) {
						statement.setString(paramIndex++, post.get("correspondence_address"));
					}
					
					if(post.get("registered_name") != null) {
						statement.setString(paramIndex++, post.get("registered_name"));
					}
					
					if(post.get("registered_no") != null) {
						statement.setString(paramIndex++, post.get("registered_no"));
					}
					
					if(post.get("trading_name") != null) {
						statement.setString(paramIndex++, post.get("trading_name"));
					}
					
					if(post.get("contact_person") != null) {
						statement.setString(paramIndex++, post.get("contact_person"));
					}
					
					if(post.get("designation") != null) {
						statement.setString(paramIndex++, post.get("designation"));
					}
					
					if(post.get("email") != null) {
						statement.setString(paramIndex++, post.get("email"));
					}
					
					if(post.get("telephone_no") != null) {
						statement.setString(paramIndex++, post.get("telephone_no"));
					}
					
					if(post.get("fax_no") != null) {
						statement.setString(paramIndex++, post.get("fax_no"));
					}
					
					if(post.get("license_no") != null) {
						statement.setString(paramIndex++, post.get("license_no"));
					}
					
					if(post.get("license_type") != null) {
						statement.setString(paramIndex++, post.get("license_type"));
					}
					
					if(post.get("state") != null) {
						statement.setString(paramIndex++, post.get("state"));
					}
					
					if(post.get("issuer") != null) {
						statement.setString(paramIndex++, post.get("issuer"));
					}
					
					if(post.get("expiry_date") != null) {
						statement.setString(paramIndex++, post.get("expiry_date"));
					}
					
					if(post.get("risk_level") != null) {
						statement.setString(paramIndex++, post.get("risk_level"));
					}
					
					if(post.get("mdr") != null) {
						statement.setString(paramIndex++, post.get("mdr"));
					}
					
					if(post.get("esapl") != null) {
						statement.setString(paramIndex++, post.get("esapl"));
					}
					
					if(post.get("ezypay") != null) {
						statement.setString(paramIndex++, post.get("ezypay"));
					}
					
					statement.setInt(paramIndex, outlet_id);
					
					statement.execute();
				} finally {
					statement.close();
				}
	
				
				Outlet outlet = getOutlet(outlet_id);
				if(outlet == null){
					System.out.println("here");
					return false;
				}
				
				JSONObject outlet_dtl_obj = new JSONObject();
				outlet_dtl_obj.put("merchant_id", outlet.getMerchant().getId());
				outlet_dtl_obj.put("trading_name", outlet.getName());
				outlet_dtl_obj.put("registered_name", outlet.getMerchant().getRegisteredName());
				outlet_dtl_obj.put("registered_no", outlet.getMerchant().getRegisteredNo());
				outlet_dtl_obj.put("license_no", outlet.getLisenceNo());
				outlet_dtl_obj.put("license_type", outlet.getLicenseType());
				outlet_dtl_obj.put("issuer", outlet.getLicenseIssuer());
				outlet_dtl_obj.put("state", outlet.getLicenseState());
				outlet_dtl_obj.put("expiry_date", outlet.getLicenseExpiry());
				outlet_dtl_obj.put("risk_level", outlet.getRiskLevel());
				outlet_dtl_obj.put("contact_person", outlet.getCTName());
				outlet_dtl_obj.put("designation", outlet.getCTDesignation());
				outlet_dtl_obj.put("email", outlet.getCTEmail());
				outlet_dtl_obj.put("telephone_no", outlet.getCTTel());
				outlet_dtl_obj.put("fax_no", outlet.getCTFax());
				outlet_dtl_obj.put("address", outlet.getAddress());
				outlet_dtl_obj.put("address_2", outlet.getAddress2());
				outlet_dtl_obj.put("address_3", outlet.getAddress3());
				outlet_dtl_obj.put("mdr_rate", outlet.getMDRRate());
				outlet_dtl_obj.put("esapl", outlet.getEsapl());
				outlet_dtl_obj.put("ezypay", outlet.getEzypay());
				outlet_dtl_obj.put("stmt_option", outlet.getStatementOpt());
				outlet_dtl_obj.put("pymt_option", outlet.getPaymentOpt());
				outlet_dtl_obj.put("other_info", outlet.getOtherInfo());
				outlet_dtl_obj.put("gst_registration", outlet.getGst_registration());
				outlet_dtl_obj.put("rmcd_approval_no", outlet.getRmcd_approval_no());
				String outlet_dtl = outlet_dtl_obj.toString();
				
				
				sql = "update caseoutlet set co_outlet_details = ?, ";
				
				if(post.get("payment_info") != null) {
					sql += "co_pymt_option = ?, ";
				}
				
				if(post.get("statement_opt") != null) {
					sql += "co_stmt_option = ?, ";
				}
				
				if(post.get("other_info") != null) {
					sql += "co_other_info = ?, ";
				}
				
				if(post.get("physical_address") != null) {
					sql += "co_address = ?, ";
				}
				
				if(post.get("payment_address") != null) {
					sql += "co_address_2 = ?, ";
				}
				
				if(post.get("correspondence_address") != null) {
					sql += "co_address_3 = ?, ";
				}
				
				if(post.get("esa_ref_no") != null) {
					sql += "co_esa_ref_no = ?, ";
				}
				
				if(post.get("no_of_terminal") != null) {
					sql += "co_num_terminal = ?, ";
				}
				
				if(post.get("terminal_rental_fee") != null) {
					sql += "co_trm_rental = ?, ";
				}
				
				if(post.get("terminal_owner") != null) {
					sql += "co_trm_owner = ?, ";
				}
				
				if(post.get("terminal_deposit_wired") != null) {
					sql += "co_trm_deposit_wired = ?, ";
				}
				
				if(post.get("terminal_deposit_wireless") != null) {
					sql += "co_trm_deposit_wireless = ?, ";
				}
				
				if(post.get("terminal_rental_deposit_wired") != null) {
					sql += "co_trm_rental_deposit_wired = ?, ";
				}
				
				if(post.get("terminal_rental_deposit_wireless") != null) {
					sql += "co_trm_rental_deposit_wireless = ?, ";
				}
				
				if(post.get("advance_terminal_rental") != null) {
					sql += "co_trm_rental_advance = ?, ";
				}
				
				if(post.get("total_cheque_amt") != null) {
					sql += "co_cheque_amt = ?, ";
				}
				
				if(post.get("bank_issuer") != null || post.get("bank_branch") != null) {
					sql += "co_cheque_issuer = ?, ";
				}
				
				if(post.get("cheque_no") != null) {
					sql += "co_cheque_no = ?, ";
				}
				
				if(post.get("cheque_date") != null) {
					sql += "co_cheque_date = ?, ";
				}
				
				if(post.get("terminal_id") != null) {
					sql += "co_terminal_id = ?, ";
				}
				
				if(post.get("terminal_id_no") != null) {
					sql += "co_terminal_id_no = ?, ";
				}
				
				if(post.get("terminal_install_date") != null) {
					sql += "co_terminal_install_date = ?, ";
				}
				
				if(post.get("mdr") != null) {
					sql += "co_mdr_rate = ?, ";
				}
				
				if(post.get("esapl") != null) {
					sql += "co_esapl = ?, ";
				}
				
				if(post.get("ezypay") != null) {
					sql += "co_ezy = ?, ";
				}
				
				if(post.get("mid_pos") != null) {
					sql += "co_mid_pos = ?, ";
				}
				
				if(post.get("mid_amex") != null) {
					sql += "co_mid_amex = ?, ";
				}
				
				sql = sql.substring(0, sql.lastIndexOf(','));
				
				sql += " where co_case_id = ? and co_outlet_id = ?";
				
				System.out.println(sql);
				System.out.println("CaseID : " + case_id);
				System.out.println("Outlet ID : " + outlet_id);
				System.out.println("Payment Info : " + post.get("payment_info"));
				
				statement = conn.prepareStatement(sql);
				
				try {
					int paramIndex = 1;
					statement.setString(paramIndex++, outlet_dtl);
					
					if(post.get("payment_info") != null) {
						statement.setString(paramIndex++, post.get("payment_info"));
					}
					
					if(post.get("statement_opt") != null) {
						statement.setString(paramIndex++, post.get("statement_opt"));
					}
					
					if(post.get("other_info") != null) {
						statement.setString(paramIndex++, post.get("other_info"));
					}
					
					if(post.get("physical_address") != null) {
						statement.setString(paramIndex++, post.get("physical_address"));
					}
					
					if(post.get("payment_address") != null) {
						statement.setString(paramIndex++, post.get("payment_address"));
					}
					
					if(post.get("correspondence_address") != null) {
						statement.setString(paramIndex++, post.get("correspondence_address"));
					}
					
					if(post.get("esa_ref_no") != null) {
						statement.setString(paramIndex++, post.get("esa_ref_no"));
					}
					
					if(post.get("no_of_terminal") != null) {
						statement.setString(paramIndex++, post.get("no_of_terminal"));
					}
					
					if(post.get("terminal_rental_fee") != null) {
						statement.setString(paramIndex++, post.get("terminal_rental_fee"));
					}
					
					if(post.get("terminal_owner") != null) {
						statement.setString(paramIndex++, post.get("terminal_owner"));
					}
					
					if(post.get("terminal_deposit_wired") != null) {
						statement.setDouble(paramIndex++, (post.get("terminal_deposit_wired").length() <= 0 ? 0.00 : Double.parseDouble(post.get("terminal_deposit_wired"))));
					}
					
					if(post.get("terminal_deposit_wireless") != null) {
						statement.setDouble(paramIndex++, (post.get("terminal_deposit_wireless").length() <= 0 ? 0.00 : Double.parseDouble(post.get("terminal_deposit_wireless"))));
					}
					
					if(post.get("terminal_rental_deposit_wired") != null) {
						statement.setDouble(paramIndex++, (post.get("terminal_rental_deposit_wired").length() <= 0 ? 0.00 : Double.parseDouble(post.get("terminal_rental_deposit_wired"))));
					}
					
					if(post.get("terminal_rental_deposit_wireless") != null) {
						statement.setDouble(paramIndex++, (post.get("terminal_rental_deposit_wireless").length() <= 0 ? 0.00 : Double.parseDouble(post.get("terminal_rental_deposit_wireless"))));
					}
					
					if(post.get("advance_terminal_rental") != null) {
						statement.setDouble(paramIndex++, (post.get("advance_terminal_rental").length() <= 0 ? 0.00 : Double.parseDouble(post.get("advance_terminal_rental"))));
					}
					
					if(post.get("total_cheque_amt") != null) {
						statement.setDouble(paramIndex++, (post.get("total_cheque_amt").length() <= 0 ? 0.00 : Double.parseDouble(post.get("total_cheque_amt"))));
					}
					
					if(post.get("bank_issuer") != null || post.get("bank_branch") != null) {
						statement.setString(paramIndex++, (post.get("bank_issuer") != null ? post.get("bank_issuer") : "") + "|" + (post.get("bank_branch") != null ? post.get("bank_branch") : ""));
					}
					
					if(post.get("cheque_no") != null) {
						statement.setString(paramIndex++, post.get("cheque_no"));
					}
					
					if(post.get("cheque_date") != null) {
						statement.setString(paramIndex++, post.get("cheque_date"));
					}
					
					if(post.get("terminal_id") != null) {
						statement.setString(paramIndex++, post.get("terminal_id"));
					}
					
					if(post.get("terminal_id_no") != null) {
						statement.setString(paramIndex++, post.get("terminal_id_no"));
					}
					
					if(post.get("terminal_install_date") != null) {
						statement.setString(paramIndex++, post.get("terminal_install_date"));
					}
					
					if(post.get("mdr") != null) {
						statement.setString(paramIndex++, post.get("mdr"));
					}
					
					if(post.get("esapl") != null) {
						statement.setString(paramIndex++, post.get("esapl"));
					}
					
					if(post.get("ezypay") != null) {
						statement.setString(paramIndex++, post.get("ezypay"));
					}
					
					if(post.get("mid_pos") != null) {
						statement.setString(paramIndex++, post.get("mid_pos"));
					}
					
					if(post.get("mid_amex") != null) {
						statement.setString(paramIndex++, post.get("mid_amex"));
					}
					
					
					statement.setInt(paramIndex++, case_id);
					statement.setInt(paramIndex, outlet_id);
					
					statement.execute();
				} finally {
					statement.close();
				}
				
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return true;
			
		} catch (Exception e){
			e.printStackTrace();
			this.message = e.getMessage();
			return false;
		}
	}
	
	
	public boolean deleteCaseOutlet(int case_id, int outlet_id, String user){
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String sql = "delete from caseoutlet where co_case_id = ? and co_outlet_id = ?";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					statement.setInt(1, case_id);
					statement.setInt(2, outlet_id);
					
					statement.execute();
				} finally {
					statement.close();
				}
				
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return true;
			
		} catch (Exception e){
			this.message = e.getMessage();
			return false;
		}
	}
	
	public ArrayList<CaseOutlet> getCaseOutlets(int case_id) {
		ArrayList<CaseOutlet> coArr = new ArrayList<CaseOutlet>();
		
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String sql = "select * from caseoutlet where co_case_id = ?";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					statement.setInt(1, case_id);
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						while(resultSet.next()){
							CaseOutlet a = new CaseOutlet();
							
							a.setCaseId(Long.parseLong(resultSet.getString("co_case_id")));
							a.setOutletDtl(resultSet.getString("co_outlet_details"));
							a.setESARefNo(resultSet.getString("co_esa_ref_no"));
							a.setNumTerminal(resultSet.getString("co_num_terminal"));
							a.setTrmRental(resultSet.getString("co_trm_rental"));
							a.setTrmOwner(resultSet.getString("co_trm_owner"));
							a.setTrmDepositWired(resultSet.getString("co_trm_deposit_wired"));
							a.setTrmDepositWireless(resultSet.getString("co_trm_deposit_wireless"));
							a.setTrmRentalDepositWired(resultSet.getString("co_trm_rental_deposit_wired"));
							a.setTrmRentalDepositWireless(resultSet.getString("co_trm_rental_deposit_wireless"));
							a.setTrmRentalAdvance(resultSet.getString("co_trm_rental_advance"));
							a.setChequeAmt(resultSet.getString("co_cheque_amt"));
							a.setChequeIssuer(resultSet.getString("co_cheque_issuer"));
							a.setChequeNo(resultSet.getString("co_cheque_no"));
							a.setChequeDate(resultSet.getString("co_cheque_date"));
							a.setRecommendation(resultSet.getString("co_recommendation"));
							
							JSONObject obj = (JSONObject)JSONValue.parse(resultSet.getString("co_outlet_details"));
			
							Outlet o = new Outlet();
							o.setId(Long.parseLong(resultSet.getString("co_outlet_id")));
							o.setName(obj.get("trading_name").toString());
							
							System.out.println("set GST fields OutletFactory.java");
							try{
								o.setGst_registration(obj.containsKey("gst_registration") ? obj.get("gst_registration").toString() : null);
							}catch (Exception e1){
								System.out.println("set gst_registration error");
							}
							try{
								o.setRmcd_approval_no(obj.containsKey("rmcd_approval_no") ? obj.get("rmcd_approval_no").toString() : null);
							}catch (Exception e1){
								System.out.println("set rmcd_approval_no error");
							}
							
							o.setLicenseNo(obj.get("license_no").toString());
							o.setLicenseType(obj.get("license_type").toString());
							o.setLicenseIssuer(obj.get("issuer").toString());
							o.setLicenseState(obj.get("state").toString());
							o.setLicenseExpiry(obj.get("expiry_date").toString());
							o.setRiskLevel(obj.get("risk_level").toString());
							o.setCTName(obj.get("contact_person").toString());
							o.setCTDesignation(obj.get("designation").toString());
							o.setCTEmail(obj.get("email").toString());
							o.setCTTel(obj.get("telephone_no").toString());
							o.setCTFax(obj.get("fax_no").toString());
							o.setPaymentOpt(resultSet.getString("co_pymt_option"));
							o.setStatementOpt(resultSet.getString("co_stmt_option"));
							o.setOtherInfo(resultSet.getString("co_other_info"));
							o.setAddress(resultSet.getString("co_address"));
							o.setAddress2(resultSet.getString("co_address_2"));
							o.setAddress3(resultSet.getString("co_address_3"));
							o.setMDRRate(resultSet.getString("co_mdr_rate"));
							o.setEsapl(resultSet.getString("co_esapl"));
							o.setEzypay(resultSet.getString("co_ezy"));
							
							Merchant m = new Merchant();
							m.setId(Long.parseLong(obj.get("merchant_id").toString()));
							m.setRegisteredName(obj.get("registered_name").toString());
							m.setRegisteredNo(obj.get("registered_no").toString());
							
							o.setMerchant(m);
							
							a.setOutlet(o);
							
							coArr.add(a);
						}
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
				
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return coArr;
			
		} catch (Exception e){
			this.message = e.getMessage();
			return null;
		}
	}
	
	public boolean editCaseOutletRecom(int case_id, int outlet_id, String recom){
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String sql = "update caseoutlet set co_recommendation = ? where co_case_id = ? and co_outlet_id = ?";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					statement.setString(1, recom);
					statement.setInt(2, case_id);
					statement.setInt(3, outlet_id);
					
		            statement.execute();
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return true;
			
		} catch (Exception e){
			this.message = e.getMessage();
			return false;
		}
	}
	
	public boolean editCaseOutletPOSForm(int case_id, int outlet_id, String pos_form){
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String sql = "update caseoutlet set co_pos_form = ? "
						+ "where co_case_id = ? "
						+ "	and co_outlet_id = ?";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					statement.setString(1, pos_form);
					statement.setInt(2, case_id);
					statement.setInt(3, outlet_id);
					
		            statement.execute();
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return true;
			
		} catch (Exception e){
			this.message = e.getMessage();
			return false;
		}
	}
	
	public boolean editCaseOutletPOSForm(int case_id, int outlet_id, String pos_form, String terminalNum, String terminalID){
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String sql = "update caseoutlet set co_pos_form = ?, "
						+ "	co_terminal_id = ?, "
						+ " co_terminal_id_no = ? "
						+ "where co_case_id = ? "
						+ "	and co_outlet_id = ?";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					statement.setString(1, pos_form);
					statement.setString(2, terminalID);
					statement.setString(3, terminalNum);
					statement.setInt(4, case_id);
					statement.setInt(5, outlet_id);
					
		            statement.execute();
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return true;
			
		} catch (Exception e){
			this.message = e.getMessage();
			return false;
		}
	}
	
	public boolean editPOSFormSerialNo(int case_id, int outlet_id, String vendor_name, String serial_no){
		try {
			boolean hasResult = false;
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String sql = "select * from  caseposform "
						+ "where pos_case_id = ? "
						+ "and pos_outlet_id = ? " 
						+ "and pos_vendor_name = ? ";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					statement.setInt(1, case_id);
					statement.setInt(2, outlet_id);
					statement.setString(3, vendor_name);
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						hasResult = resultSet.next();
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
				
				
				if(hasResult == false) {
					sql = "insert into caseposform(pos_case_id, pos_outlet_id, pos_vendor_name, pos_serial_no) "
							+ "values(?, ?, ?, ?)";
					
					statement = conn.prepareStatement(sql);
					
					try {
						statement.setInt(1, case_id);
						statement.setInt(2, outlet_id);
						statement.setString(3, vendor_name);
						statement.setString(4, serial_no);
						
			            statement.execute();
					} finally {
						statement.close();
					}
				}
				
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return true;
			
		} catch (Exception e){
			this.message = e.getMessage();
			return false;
		}
	}
	
	
	public boolean editCaseOutletInstallForm(int case_id, int outlet_id, String pos_install, String terminal_install_date){
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String sql = "update caseoutlet set co_installation = ?, "
						+ "co_terminal_install_date = ? "
						+ "where co_case_id = ? "
						+ "	and co_outlet_id = ?";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					statement.setString(1, pos_install);
					statement.setString(2, terminal_install_date);
					statement.setInt(3, case_id);
					statement.setInt(4, outlet_id);
					
		            statement.execute();
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return true;
			
		} catch (Exception e){
			this.message = e.getMessage();
			return false;
		}
	}
	
	
	public boolean install_note_submit(int case_id, int outlet_id, String vendor_name, String remark, String status, String reschd_date, String user){
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String sql = "insert into caseinstallnote(in_case_id, in_outlet_id, in_vendor_name, in_remark, in_status, in_reschedule_date, in_created_by, in_created_date) "
					+ "values(?, ?, ?, ?, ?, ?, ?, sysdate)";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					statement.setLong(1, case_id);
					statement.setLong(2, outlet_id);
					statement.setString(3,  vendor_name);
					statement.setString(4, remark.length() > 0 ? remark : null);
					statement.setString(5, status);
					statement.setString(6, reschd_date.length() > 0 ? reschd_date : null);
					statement.setString(7, user);
					
		            statement.execute();
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			return true;
			
		} catch (Exception e){
			this.message = e.getMessage();
			return false;
		}
	}
	
	public char randomCharacter(){		
		Random r = new Random();
		String alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
		int N = alphabet.length();

		return alphabet.charAt(r.nextInt(N));
	}
	
	public String randomNumber(int min, int max) {

	    // Usually this can be a field rather than a method variable
	    Random rand = new Random();

	    // nextInt is normally exclusive of the top value,
	    // so add 1 to make it inclusive
	    int randomNum = rand.nextInt((max - min) + 1) + min;

	    return Integer.toString(randomNum);
	}
	
	public String getCreatorName(int case_id) {
		try {
	        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
	        String connectionPool = ConfigManager.getInstance().getConnectionPool();
	        DBConnectionManager connMgr = DBConnectionManager.getInstance();
	        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
	        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
			
			try {
				String sql = "select u.NAME from CASEHIST c " +
						"inner join TBLUSER u on c.CH_CREATED_BY = u.LOGIN_ID " +
						"where CH_CASE_ID = ? and CH_STATUS = 'PUT UP' ";
				
				PreparedStatement statement = conn.prepareStatement(sql);
				
				try {
					statement.setInt(1, case_id);
					
					ResultSet resultSet = statement.executeQuery();
					
					try {
						if(resultSet.next()){
							String creator = resultSet.getString("NAME");
							return creator;
						}
					} finally {
						resultSet.close();
					}
					
				} finally {
					statement.close();
				}
			} finally {
				connMgr.freeConnection(connectionPool, conn);
			}
			
			this.message = "Case Outlet not found.";
			
		} catch (Exception e){
			this.message = e.getMessage();
		}
		
		return "";
	}
	
}
